package com.youens.navigationdrawertest.database

import android.database.sqlite.SQLiteDatabase
import android.text.format.DateUtils
import android.util.Log
import com.youens.navigationdrawertest.ui.favorite.Record
import com.youens.navigationdrawertest.ui.wrongword.WrongWord
import java.io.File
import java.lang.Exception
import java.sql.Date

class DataBaseUtils {
    public constructor(fileDir: String){
        initDateBase(fileDir)
    }
    private lateinit var DATABASE_NAME :File
    private lateinit var database:SQLiteDatabase
    private val wordsTableSQL = """
        CREATE TABLE [words](
            [id] INTEGER PRIMARY KEY AUTOINCREMENT,
            [word] VARCHAR(50) NOT NULL,
            [result] VARCHAR(50) NOT NULL,
            [time] Integer,
            [img_path] TEXT,
            [know] Integer default 0,
            CONSTRAINT [sqlite_autoindex_words_id]
        )
    """.trimIndent()

    private val favoriteTableSQL = """
        CREATE TABLE [favorite](
            [id] INTEGER PRIMARY KEY,
            [time] Integer,
            FOREIGN KEY(id) REFERENCES words(id)
        )
    """.trimIndent()

    private val wrongWordsTableSQL = """
        CREATE TABLE [wrong_words](
            [id] INTEGER PRIMARY KEY,
            [correct] INTEGER DEFAULT 0,
            [wrong] INTEGER DEFAULT 0,
            FOREIGN KEY(id) REFERENCES words(id)
        )
    """.trimIndent()

    private val punchChockTableSQL = """
        CREATE TABLE [punch_chock](
            [id] INTEGER PRIMARY KEY DEFAULT 1,
            [total] INTEGER DEFAULT 0,
            [persist] INTEGER DEFAULT 0,
            [time] Integer,
            [translate_time] Integer,
            [translate_num] Integer
        )
    """.trimIndent()

    private fun initDateBase(fileDir: String){

        DATABASE_NAME = File(fileDir + "/test.db")
        if(!DATABASE_NAME.exists()){
            database = SQLiteDatabase.openOrCreateDatabase(DATABASE_NAME, null)
            database.execSQL(wordsTableSQL)
            database.execSQL(favoriteTableSQL)
            database.execSQL(wrongWordsTableSQL)
            database.execSQL(punchChockTableSQL)

            database.execSQL("insert into punch_chock(id,total,persist,time,translate_time,translate_num) values(?,?,?,?,?,?)", arrayOf(1, 0, 0,  java.util.Date().time-24*60*60*1000*2, java.util.Date().time,0))
            //Log.d("TAG","database has been init!")
        }else{
            database = SQLiteDatabase.openOrCreateDatabase(DATABASE_NAME, null)
        }
    }


    /**
     * 翻译调用，添加到单词本
     */
    public fun addWords(word: String, result:String,time:Long, img_path:String){
        val sql = "insert into words(word, result, time, img_path, know) values(?,?,?,?,0)"
        try {
            database.execSQL(sql, arrayOf(word, result,time, img_path))
        }catch (e:Exception){
            Log.e("TAG",e.toString())
        }
    }

    /**
     * 修改word状态：
     *      0 不认识
     *      1 模糊
     *      2 认识
     */
    public fun updateWordStatue(id: Int,statue:Int){
        var sql = "update words set know=? where id=?"
        database.execSQL(sql, arrayOf(statue.toString(),id.toString()))
    }

    /**
     * 获取单词id
     */
    public fun getWordsRandomly(num:Int):MutableList<Record>{
        var sql = "select id,word,result from words where know=0 or know=1 order by random() limit ?"
        var cursor = database.rawQuery(sql, arrayOf(num.toString()))
        val randomWords = mutableListOf<Record>()
        if (cursor.moveToFirst()){
            do {
                var id = cursor.getInt(0)
                var word = cursor.getString(1)
                var result = cursor.getString(2)
                randomWords.add(Record(id,word,result))
            }while (cursor.moveToNext())
        }
        cursor.close()
        return randomWords

    }

    public fun getWordsId(word: String):Int{
        val sql = "select id from words where word=?"
        var id = 0
        val cursor = database.rawQuery(sql, arrayOf(word))
        if(cursor.moveToFirst()){
            id = cursor.getInt(0)
        }
        else{
            id = -1
        }
        cursor.close()
        return id
    }

    /**
     * 取消收藏
     */
    public fun cancelFavourite(id: Int){
        val sql = "delete from favorite where id=?"
        database.execSQL(sql, arrayOf(id))
    }

    /**
     * 添加收藏
     */
    public fun addFavourite(id: Int, time: Long){
        val sql = "insert into favorite(id, time) values(?,?)"
        try {
            database.execSQL(sql, arrayOf(id, time))
        }catch (e:Exception){
            Log.e("TAG",e.toString())
        }

    }
    /**
     * 获取所有收藏单词
     */
    public fun getAllFavorite():MutableList<Record>{
        val sql = "select word,result from words,favorite where favorite.id=words.id"
        val cursor = database.rawQuery(sql,null)
        val allFavorite = mutableListOf<Record>()
        if (cursor.moveToFirst()){
            do {
                var word = cursor.getString(0)
                var result = cursor.getString(1)
                allFavorite.add(Record(word,result))
            }while (cursor.moveToNext())
        }
        cursor.close()
        return allFavorite
    }
    /**
     * 获取所有收藏单词
     */
    public fun getAllFavourite(): MutableMap<Int, List<Any>> {
        val cursor = database.rawQuery("select favorite.id,word,result,favorite.time,img_path from favorite,words where favorite.id=words.id",null)
        val allFavourite = mutableMapOf<Int,List<Any>>()
        if (cursor.moveToFirst()){
            do {
                var id = cursor.getInt(0)
                var word = cursor.getString(1)
                var result = cursor.getString(2)
                var time = cursor.getLong(3)
                var img_path = cursor.getString(4)
                allFavourite.put(id, listOf(word,result,time,img_path))
            }while (cursor.moveToNext())
        }
        cursor.close()
        return allFavourite
    }

    /**
     * 获取所有错词本单词
     */
    public fun getAllWrongWords(): MutableList<WrongWord> {
        val cursor = database.rawQuery("select wrong_words.id,word,result,correct,wrong from wrong_words,words where wrong_words.id=words.id",null)
        val allWrongWords = mutableListOf<WrongWord>()
        if(cursor.moveToFirst()){
            do {
                var id = cursor.getInt(0)
                var word = cursor.getString(1)
                var result = cursor.getString(2)
                var correct = cursor.getInt(3)
                var wrong = cursor.getInt(4)
                allWrongWords.add(WrongWord(id,word,result,correct,wrong))
            }while (cursor.moveToNext())
        }
        cursor.close()
        return allWrongWords
    }

    public fun getWongWordsRandomly(num:Int):MutableList<Record>{
        var sql = "select words.id,words.word,words.result from words,wrong_words where words.id=wrong_words.id order by random() limit ?"
        var cursor = database.rawQuery(sql, arrayOf(num.toString()))
        val randomWords = mutableListOf<Record>()
        if (cursor.moveToFirst()){
            do {
                var id = cursor.getInt(0)
                var word = cursor.getString(1)
                var result = cursor.getString(2)
                randomWords.add(Record(id,word,result))
            }while (cursor.moveToNext())
        }
        cursor.close()
        return randomWords

    }

    /**
     * 添加单词到错词本（无需用户手动）
     */
    public fun addWrongWord(id:Int){
        val sql = "insert into wrong_words(id,correct,wrong) values(?, 0, 1)"
        try {
            database.execSQL(sql, arrayOf(id))
        }catch (e:Exception){
            Log.e("TAG",e.toString())
        }
    }

    /**
     * 将单词从错词本中移除
     */
    public fun deleteWrongWord(id:Int){
        val sql = "delete from wrong_words where id=?"
        try {
            database.execSQL(sql, arrayOf(id))
        }catch (e:Exception){
            Log.e("TAG",e.toString())
        }

    }

    /**
     * 更新错词本
     */
    public fun updateWrongWord(id:Int, correct:Int, wrong:Int){
        val sql = "update wrong_words set correct=correct+?,wrong=wrong+? where id=?"
        database.execSQL(sql, arrayOf(correct,wrong,id))
    }

    /**
     * 更新打卡表的最近识图时间和某天识图数量
     */
    public fun updatePunchChockProgress(time: Long){
        val cursor = database.rawQuery("select translate_time from punch_chock where id=1",null)
        cursor.moveToFirst()
        val translate_time = java.util.Date(cursor.getLong(0))
        cursor.close()
        val now = java.util.Date(time)
        if (translate_time.year == now.year && translate_time.month==now.month && translate_time.day == now.day){
            val sql = "update punch_chock set translate_time=?, translate_num=translate_num+1 where id=1"
            database.execSQL(sql, arrayOf(time))
        } else{
            val sql = "update punch_chock set translate_time=?, translate_num=1 where id=1"
            database.execSQL(sql, arrayOf(time))
        }

    }

    public fun getTranslateNum():Int{
        val cursor = database.rawQuery("select translate_num,translate_time,time from punch_chock where id=1",null)
        cursor.moveToFirst()
        var translate_num = cursor.getInt(0)
        var translateTime = java.util.Date(cursor.getLong(1))
        var punckClockTime = java.util.Date(cursor.getLong(2))
        cursor.close()
        var now = java.util.Date()

        var punckClockDay = String.format("%tj",punckClockTime).toInt()
        var nowDay = String.format("%tj",now).toInt()
        if((nowDay - punckClockDay)<2){

        }else{
            val sql = "update punch_chock set persist=0 where id=1"
            database.execSQL(sql)
        }

        if(translateTime.year == now.year && translateTime.month==now.month && translateTime.day == now.day){
            return translate_num
        }else{
            database.execSQL("update punch_chock set translate_num=0 where id=1")
            return 0
        }


    }

    public fun getIsPunchClock():Boolean{
        val cursor = database.rawQuery("select time from punch_chock where id=1",null)
        cursor.moveToFirst()
        var time = java.util.Date(cursor.getLong(0))
        cursor.close()
        var now = java.util.Date()
        return (time.year == now.year && time.month==now.month && time.day == now.day)
    }


    /**
     * 修改打卡记录
     */
    public fun updatePunchChock(time:Long){
        val cursor = database.rawQuery("select time from punch_chock where id=1",null)
        cursor.moveToFirst()
        var punckClockTime = java.util.Date(cursor.getLong(0))
        cursor.close()
        var now = java.util.Date(time)
        var punckClockDay = String.format("%tj",punckClockTime).toInt()
        var nowDay = String.format("%tj",now).toInt()
        if((nowDay - punckClockDay)<2){
            val sql = "update punch_chock set total=total+1, persist=persist+1, time=? where id=1"
            database.execSQL(sql, arrayOf(time))
        }else{
            val sql = "update punch_chock set total=total+1, persist=1, time=? where id=1"
            database.execSQL(sql, arrayOf(time))
        }
    }

    public fun getTotalAndPersistDays():List<Int>{
        val cursor = database.rawQuery("select total,persist from punch_chock where id=1",null)
        cursor.moveToFirst()
        val total = cursor.getInt(0)
        val persist = cursor.getInt(1)
        cursor.close()
        return listOf(total, persist)
    }

}